package cn.chinaunicom.sdsi.upfc_demp.sys.controller;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.SAXException;

import cn.chinaunicom.sdsi.frm.base.util.ExcelUtils;

@Controller
@RequestMapping("Excel")
public class SysExcelDemoController {
    @Autowired
    ExcelUtils excelUtils;

    @RequestMapping("excelUtil")
    public String excelUtil() {
        return "frm/sys/sysDemoController/excelUtil";
    }

    /**
     * @throws IOException
     * @Description:excel导出 Demo 2003版本单sheet最大记录为65535，若数据超出请升级Excel版本，且文件不存在请自行判定创建。
     *//*
       * @RequestMapping("output")
       * 
       * @ResponseBody public Map outputExcel(@RequestParam("excelFile") MultipartFile file) throws IOException{
       * InputStream in = file.getInputStream(); String filename=file.getOriginalFilename(); Workbook
       * w=excelUtils.outputExcel("select '' from dual", 1, 0, in,filename,0); w.write(new
       * FileOutputStream("D:/test1.xls")); w.close(); Map m=new HashMap(); m.put("filepath", "D:/test1.xls");
       * m.put("filename", filename); m.put("sql", "select * from testexcl"); return m; }
       */
    @RequestMapping("down")
    public void download(HttpServletRequest request, HttpServletResponse response, @RequestParam("filename") String filename,
            @RequestParam("sql") String sql) {
        String sp = File.separator;
        String filepath = request.getSession().getServletContext().getRealPath(sp) + sp + "resource" + sp + "files" + sp
                + "sys_permission.xls";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));
            Workbook w = excelUtils.outputExcel(sql, filepath, 1, 0, 0);
            w.write(response.getOutputStream());
            w.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @throws SAXException
     * @throws ParserConfigurationException
     * @throws OpenXML4JException
     * @throws IOException
     * @Description:excel导出 Demo
     */
    @ResponseBody
    @RequestMapping("input")
    public String inputExcel(@RequestParam("excelFile") MultipartFile file)
            throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
        InputStream in = file.getInputStream();
        String filename = file.getOriginalFilename();
        // 小数据量示例
        List[] result = excelUtils.inputExcel(in, filename, 0, 1, 0);
        for (int i = 0; i < result.length; i++) {
            if (null == result[i]) {//判断是否空行
                continue;
            }
            for (int j = 0; j < result[i].size(); j++) {
                System.out.print(result[i].get(j) + "\t");
            }
        }
        // 大数据量示例
        /*
         * List<String[]> result = excelUtils.inputExcel("D:/test.xlsx", "Sheet1", 18); for (int i = 0; i <
         * result.size(); i++) { for (int j = 0; j < result.get(i).length; j++) { System.out.print(result.get(i)[j] +
         * "\t"); } System.out.println(); }
         */
        return "success";
    }
}
